﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FFS.Model;
using FFS.IDAL;
using FFS.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace FFS.SQLDAL
{
    public class FinanceDetailDAO:IFinanceDetailDAO
    {
        private const string FINANCE_DETAIL_ID = "@FinanceDetailID";
        private const string FAMILY_MEMBER_ID = "@FamilyMemberID";
        private const string FAMILY_MEMBER_NAME = "@FamilyMemberName";
        private const string FINANCE_ITEM_KEY = "@FinanceItemKey";
        private const string FINANCE_ITEM_NAME = "@FinanceItemName";
        private const string FINANCE_TYPE_KEY = "@FinanceTypeKey";
        private const string FINANCE_TYPE_NAME = "@FinanceTypeName";
        private const string FINANCE_AMOUNT = "@FinanceAmount";
        private const string FINANCE_REMARK = "@FinanceRemark";
        private const string INPUT_USER = "@input_user";

        private const string SELECT_ALL_FINANCE_DETAIL = @"  SELECT FinanceDetailID
                                                                  ,FamilyMemberID
                                                                  ,FamilyMemberName
                                                                  ,FinanceItemKey
                                                                  ,FinanceItemName
                                                                  ,FinanceTypeKey
                                                                  ,FinanceTypeName
                                                                  ,FinanceAmount
                                                                  ,FinanceRemark
                                                                  ,input_date
                                                                  ,input_user
                                                              FROM dbo.View_ALLFinanceDetail  
                                                                where 1=1  ";

        private const string UPDATE_FINANCE_DETAIL = @" UPDATE [dbo].[FinanceDetail]
                                                       SET [FamilyMemberID] = @FamilyMemberID 
                                                          ,[FinanceItemKey] = @FinanceItemKey 
                                                          ,[FinanceTypeKey] = @FinanceTypeKey 
                                                          ,[FinanceAmount] = @FinanceAmount 
                                                          ,[FinanceRemark] = @FinanceRemark 
                                                          ,[input_date] = getdate() 
                                                          ,[input_user] = @input_user 
                                                     WHERE is_deleted = 0 
                                                     and [FinanceDetailID] = @FinanceDetailID ";

        private const string INSERT_FINANCE_DETAIL = @"  
                                                    INSERT INTO [dbo].[FinanceDetail]
                                                               ([FamilyMemberID]
                                                               ,[FinanceItemKey]
                                                               ,[FinanceTypeKey]
                                                               ,[FinanceAmount]
                                                               ,[FinanceRemark]
                                                               ,[is_deleted]
                                                               ,[input_date]
                                                               ,[input_user])
                                                         VALUES
                                                               (@FamilyMemberID 
                                                               ,@FinanceItemKey 
                                                               ,@FinanceTypeKey 
                                                               ,@FinanceAmount 
                                                               ,@FinanceRemark 
                                                               ,0  
                                                               ,GETDATE()  
                                                               ,@input_user ) 


                                                  ";
        private const string DELETE_FINANCE_DETAIL = @" UPDATE FinanceDetail 
                                                    set is_deleted = 1,input_date = getdate() ,input_User = @input_user 
                                                    where is_deleted = 0 and FinanceDetailID = @FinanceDetailID";
        private const string IS_EXISTS_FINANCE_DETAIL = SELECT_ALL_FINANCE_DETAIL + @" AND  FamilyMemberID = @FamilyMemberID AND FinanceItemKey = @FinanceItemKey AND FinanceTypeKey = @FinanceTypeKey ";

        private List<FinanceDetailInfo> GetQueryList(CommandType cmdType, string sqlText, params SqlParameter[] param)
        {
            List<FinanceDetailInfo> financeDetail = new List<FinanceDetailInfo>();
            using (SqlDataReader reader = DBHelper.ExecuteReader(cmdType, sqlText, param))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        FinanceDetailInfo detail = new FinanceDetailInfo();
                        detail.FinanceDetailID = reader.GetInt32(0);
                        detail.FamilyMemberID = reader.GetInt32(1);
                        detail.FamilyMemberName = reader.GetString(2);
                        detail.FinanceItemKey = reader.GetString(3);
                        detail.FinanceItemName = reader.GetString(4);
                        detail.FinanceTypeKey = reader.GetString(5);
                        detail.FinanceTypeName = reader.GetString(6);
                        detail.FinanceAmount = reader.GetDecimal(7);
                        detail.FinanceRemark = reader.GetString(8);
                        detail.Input_Date = reader.GetDateTime(9);
                        detail.Input_User = reader.GetString(10);
                        financeDetail.Add(detail);
                    }
                }
            }

            return financeDetail;
        }


        #region IFinanceDetailDAO 成员

        public List<FinanceDetailInfo> GetAllFinanceDetails()
        {
            return this.GetQueryList(CommandType.Text,SELECT_ALL_FINANCE_DETAIL,null);
        }

        public List<FinanceDetailInfo> GetFinanceDetailsByFamilyMemberName(string familyMember)
        {
            string sqlText = SELECT_ALL_FINANCE_DETAIL + " and FamilyMemberName = @FamilyMemberName  ";
            SqlParameter param = new SqlParameter(FAMILY_MEMBER_NAME,familyMember);

            return this.GetQueryList(CommandType.Text,sqlText,param);

        }

        public List<FinanceDetailInfo> GetFinanceDetailsByFinanceItemKey(string financeItemKey)
        {
            string sqlText = SELECT_ALL_FINANCE_DETAIL + " and FinanceItemKey = @FinanceItemKey  ";
            SqlParameter param = new SqlParameter(FINANCE_ITEM_KEY, financeItemKey);

            return this.GetQueryList(CommandType.Text, sqlText, param);
        }

        public List<FinanceDetailInfo> GetFinanceDetailsByFinanceItemName(string financeItemName)
        {
            string sqlText = SELECT_ALL_FINANCE_DETAIL + " and FinanceItemName = @FinanceItemName  ";
            SqlParameter param = new SqlParameter(FINANCE_ITEM_NAME, financeItemName);

            return this.GetQueryList(CommandType.Text, sqlText, param);
        }

        public List<FinanceDetailInfo> GetFinanceDetailsBySearchContent(string searchContent)
        {
            string sqlText = SELECT_ALL_FINANCE_DETAIL + " and " + searchContent ;
            return this.GetQueryList(CommandType.Text,sqlText,null);
        }

        public int CreateFinanceDetail(FinanceDetailInfo detail)
        {
            SqlParameter[] param = {
                                    new SqlParameter(FAMILY_MEMBER_ID,detail.FamilyMemberID),
                                    new SqlParameter(FINANCE_ITEM_KEY,detail.FinanceItemKey),
                                    new SqlParameter(FINANCE_TYPE_KEY,detail.FinanceTypeKey),
                                    new SqlParameter(FINANCE_AMOUNT,detail.FinanceAmount),
                                    new SqlParameter(FINANCE_REMARK,detail.FinanceRemark),
                                    new SqlParameter(INPUT_USER,detail.Input_User)
                                 };

            return DBHelper.ExecuteNonQuery(CommandType.Text,INSERT_FINANCE_DETAIL,param);
        }

        public int ModifyFinanceDetail(FinanceDetailInfo detail)
        {
            SqlParameter[] param = {
                                    new SqlParameter(FAMILY_MEMBER_ID,detail.FamilyMemberID),
                                    new SqlParameter(FINANCE_ITEM_KEY,detail.FinanceItemKey),
                                    new SqlParameter(FINANCE_TYPE_KEY,detail.FinanceTypeKey),
                                    new SqlParameter(FINANCE_AMOUNT,detail.FinanceAmount),
                                    new SqlParameter(FINANCE_REMARK,detail.FinanceRemark),
                                    new SqlParameter(INPUT_USER,detail.Input_User),
                                    new SqlParameter(FINANCE_DETAIL_ID,detail.FinanceDetailID)
                                 };

            return DBHelper.ExecuteNonQuery(CommandType.Text, UPDATE_FINANCE_DETAIL, param);
        }

        public int DeteleFinanceDetail(FinanceDetailInfo detail)
        {
            SqlParameter[] param = {
                                    new SqlParameter(INPUT_USER,detail.Input_User),
                                    new SqlParameter(FINANCE_DETAIL_ID,detail.FinanceDetailID)
                                 };

            return DBHelper.ExecuteNonQuery(CommandType.Text, DELETE_FINANCE_DETAIL, param);
        }

        public bool IsExistsFinanceDetails(int FamilyMemberID, string FinanceItemKey, string FinanceTypeKey)
        {
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter(FAMILY_MEMBER_ID,FamilyMemberID);
            param[1] = new SqlParameter(FINANCE_ITEM_KEY,FinanceItemKey);
            param[2] = new SqlParameter(FINANCE_TYPE_KEY,FinanceTypeKey);

            if (this.GetQueryList(CommandType.Text, IS_EXISTS_FINANCE_DETAIL, param).Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion
    }
}
